from openpyxl import Workbook

class ExcelAnalysis():
    # 根据传入的内容获取所在行数
    @classmethod
    def get_row_index(self, work_book: Workbook, sheet_name, data):
        row_index_list = []
        row_num = work_book[sheet_name].max_row
        column_num = work_book[sheet_name].max_column
        for row in range(1, row_num + 1):
            for column in range(1, column_num + 1):
                if work_book[sheet_name].cell(row, column).value == data:
                    row_index_list.append(row)
        return row_index_list

    # 获取单元格的数据
    @classmethod
    def get_cell_value(self, work_book: Workbook, sheet_name, row, column):
        value = work_book[sheet_name].cell(row, column).value
        return value

    # 获取excel表头
    @classmethod
    def get_excel_title(self, work_book:Workbook, sheet_name):
        rows = list(work_book[sheet_name].rows)  # 获取工作表中所有行,行中的元素以元组的形式进行存放
        titles = []  # 用户存放表头信息
        for key in rows[0]:  # 遍历表头
            if key.value != '模块':
                titles.append(key.value)
        return titles

    # 根据传入的表头获取所在的列数
    @classmethod
    def get_column_index(self, work_book:Workbook, sheet_name, data):
        column_index_list = []
        row_num = work_book[sheet_name].max_row
        column_num = work_book[sheet_name].max_column
        for row in range(1, row_num + 1):
            for column in range(1, column_num + 1):
                if work_book[sheet_name].cell(row, column).value == data:
                    column_index_list.append(column)
        return column_index_list

    #获取对应行数的列数据
    @classmethod
    def get_column_value(self, work_book:Workbook, sheet_name, column, row_index_list):
        column_value = []
        if isinstance(column, int):
            for row in row_index_list:
                values_column = work_book[sheet_name].cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise TypeError('colulmn must be type int')

    #写入excel表格
    def write_cell(self, work_book:Workbook, sheet_name, index:tuple, value=None):
        row = index[0]
        column = index[1]
        if isinstance(row, int) and isinstance(column, int):
            try:
                cell_obj = work_book[sheet_name].cell(row, column)
                cell_obj.value=value
                work_book.save(self.testcase_file)
            except Exception as e:
                raise e
        else:
            raise TypeError('row and column must be type int')


